package com.gitee.deeround.jdbchelper.core.dialect.mysql;

import com.gitee.deeround.jdbchelper.core.Dialect;
import com.gitee.deeround.jdbchelper.core.dialect.BaseDialectCore;
import com.gitee.deeround.jdbchelper.out.DataType;
import com.gitee.deeround.jdbchelper.out.DbColumn;
import com.gitee.deeround.jdbchelper.out.DbTable;
import com.gitee.deeround.jdbchelper.util.StringUtils;

import java.util.*;

/**
 * Mysql数据库方言实现
 *
 * @author deeround
 * @date 2024/03/29
 */
public class MysqlDialect extends BaseDialectCore implements Dialect {

    @Override
    public String getCountSql(String sql) {
        return "SELECT COUNT(*) JH_COUNT FROM ( " + sql + " ) JH_TB ";
    }

    @Override
    public String getPageSql(String sql, int pageNum, int pageSize) {
        return sql + " LIMIT " + ((pageNum - 1) * pageSize) + ", " + pageSize + " ";
    }

    @Override
    public Map<DataType, String> getDataType() {
        Map<DataType, String> types = new HashMap<>();
        types.put(DataType.String, "varchar");
        types.put(DataType.Date, "datetime");
        types.put(DataType.Clob, "text");
        types.put(DataType.Blob, "blob");
        types.put(DataType.Integer, "int");
        types.put(DataType.Decimal, "decimal");
        types.put(DataType.Object, "");
        types.put(DataType.Char, "char");
        return types;
    }

    @Override
    public String getTableColumn(String tableName) {
        return "select column_name AS COLNAME, ordinal_position AS COLPOSITION, column_default AS DATADEFAULT, is_nullable AS NULLABLE, data_type AS DATATYPE, " +
                "character_maximum_length AS DATALENGTH, numeric_precision AS DATAPRECISION, numeric_scale AS DATASCALE, column_key AS COLKEY, column_comment AS COLCOMMENT " +
                "from information_schema.columns where table_name = '" + tableName + "' order by ordinal_position ";
    }

    @Override
    public String existsTable(String tableName) {
        return "select count(*) JH_COUNT from information_schema.tables where upper(table_name)=upper('" + tableName + "')";
    }

    @Override
    public String existsTableColumn(String tableName, String columnName) {
        return "select count(*) JH_COUNT from information_schema.columns where upper(table_name)=upper('" + tableName + "') and upper(column_name)=upper('" + columnName + "')";
    }

    @Override
    public List<String> createTable(DbTable dbTable) {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("CREATE TABLE ").append(dbTable.getTableName()).append("(");
        StringBuilder primaryStrSql = new StringBuilder(" PRIMARY KEY (");
        for (int i = 0; i < dbTable.getColumnList().size(); i++) {
            DbColumn dbColumn = dbTable.getColumnList().get(i);
            String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
            createTableSql.append(" ").append(dbColumn.getColumnName()).append(" ").append(dateTypeStr).append(" ");
            if (!"1".equals(dbColumn.getColumnNullable())) {
                createTableSql.append(" NOT NULL");
            }

            if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
                createTableSql.append(" DEFAULT ").append(dbColumn.getDataDefault());
            }
            if ("1".equals(dbColumn.getColumnKey())) {
                if (primaryStrSql.toString().endsWith("(")) {
                    primaryStrSql.append(dbColumn.getColumnName());
                } else {
                    primaryStrSql.append(",").append(dbColumn.getColumnName());
                }
            }
            createTableSql.append(" , ");
            if (i == dbTable.getColumnList().size() - 1) {
                primaryStrSql.append(")");
                createTableSql.append(primaryStrSql);
            }
        }
        createTableSql.append(")").append("  COMMENT '").append(dbTable.getTableComment()).append("'");
        return Collections.singletonList(createTableSql.toString());
    }

    @Override
    public List<String> dropTable(String tableName) {
        return Collections.singletonList("drop table " + tableName);
    }

    @Override
    public List<String> copyTable(String tableName, String newTableName) {
        return Collections.singletonList("CREATE TABLE " + newTableName + " AS SELECT * FROM " + tableName + " where 1=2");
    }

    @Override
    public List<String> modifyTableComment(String tableName, String tableComment) {
        return Collections.singletonList("alter table " + tableName + " comment '" + tableComment + "'");
    }

    @Override
    public List<String> modifyTableName(String oldTableName, String newTableName, String tableComment) {
        List<String> sqls = new ArrayList<>();
        sqls.add("alter table " + oldTableName + " rename to " + newTableName);
        if (!StringUtils.isEmpty(tableComment)) {
            sqls.addAll(modifyTableComment(newTableName, tableComment));
        }
        return sqls;
    }

    @Override
    public List<String> addTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " add " + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        }
        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sb.append(" default " + dbColumn.getDataDefault());
        }
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sb.append(" comment '" + dbColumn.getColumnComment() + "'");
        }
        sqls.add(sb.toString());
        return sqls;
    }

    @Override
    public List<String> modifyTableColumn(DbColumn dbColumn) {
        List<String> sqls = new ArrayList<>();
        String dateTypeStr = resolveDataType(dbColumn.getDataType(), dbColumn.getDataLength(), dbColumn.getDataScale());
        StringBuilder sb = new StringBuilder();
        sb.append("alter table " + dbColumn.getTableName() + " modify " + dbColumn.getColumnName() + " " + dateTypeStr);
        if (!"1".equals(dbColumn.getColumnNullable())) {
            sb.append(" not null");
        } else {
            sb.append(" null");
        }
        if (!StringUtils.isEmpty(dbColumn.getColumnComment())) {
            sb.append(" comment '" + dbColumn.getColumnComment() + "'");
        } else {
            sb.append(" comment ''");
        }
        sqls.add(sb.toString());

        if (!StringUtils.isEmpty(dbColumn.getDataDefault())) {
            sqls.add("alter table " + dbColumn.getTableName() + " ALTER COLUMN " + dbColumn.getColumnName() + " SET DEFAULT " + dbColumn.getDataDefault());
        } else {
            sqls.add("alter table " + dbColumn.getTableName() + " ALTER COLUMN " + dbColumn.getColumnName() + " DROP DEFAULT");
        }
        return sqls;
    }

    @Override
    public List<String> removeTableColumn(String tableName, String columnName) {
        return Collections.singletonList("alter table " + tableName + " drop " + columnName);
    }

    @Override
    public String mergeInto(String tableName, String newData, String on, String insertSql, String updateSql) {

        if (newData.toLowerCase().contains("select") && newData.contains(" ")) {
            newData = "select * from (" + newData + ")";
        }

        String sql = "merge into " + tableName + " T "
                + " using (" + newData + ") N "
                + " ON (" + on + ") "
                + " WHEN NOT MATCHED THEN "
                + " " + insertSql
                + " WHEN MATCHED THEN "
                + " " + updateSql;

        return sql;
    }

    @Override
    public String funDate() {
        return "sysdate";
    }

    @Override
    public String funTimestamp() {
        return "systimestamp";
    }

    @Override
    public String funGuid() {
        return "sys_guid()";
    }

    @Override
    public String funWmConcat(String columnName) {
        return "wm_concat(" + columnName + ")";
    }

}
